package com.example.mysqladmin.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

public class ExportUtils {
    
    public static byte[] exportToExcel(List<Map<String, Object>> data, List<String> columns) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Data");

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < columns.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columns.get(i));
            }

            // 填充数据
            int rowNum = 1;
            for (Map<String, Object> rowData : data) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < columns.size(); i++) {
                    Cell cell = row.createCell(i);
                    Object value = rowData.get(columns.get(i));
                    cell.setCellValue(value != null ? value.toString() : "");
                }
            }

            // 自动调整列宽
            for (int i = 0; i < columns.size(); i++) {
                sheet.autoSizeColumn(i);
            }

            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }

    public static String generateInsertSql(String tableName, List<Map<String, Object>> data, List<String> columns) {
        StringBuilder sql = new StringBuilder();
        for (Map<String, Object> row : data) {
            sql.append("INSERT INTO `").append(tableName).append("` (");
            // 列名
            sql.append(String.join("`, `", columns));
            sql.append(") VALUES (");
            
            // 值
            for (int i = 0; i < columns.size(); i++) {
                Object value = row.get(columns.get(i));
                if (value == null) {
                    sql.append("NULL");
                } else if (value instanceof Number) {
                    sql.append(value);
                } else {
                    sql.append("'").append(value.toString().replace("'", "''")).append("'");
                }
                if (i < columns.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(");\n");
        }
        return sql.toString();
    }
} 